#!/usr/bin/python3

import json as js
import sqlite3
import socket as web_net

#TODO:使用FTP 传输文件完成文件的上传下载完成设备的升级操作

glob_data_sqlite3_use={}

def creat_sql(json_file_path:str,sqlite3_db:str):
    sql_db = sqlite3_db
    json_file = open ( json_file_path, "r" )
    json_data = js.load ( json_file )
    dicData = json_data
    connect_sqlite3 = sqlite3.connect ( sql_db )
    coned = connect_sqlite3.cursor ()
    coned.execute (
        'create table if not exists rau(ID Text,name Text,mode Text, var_type Text, coefficient, len, V0, min, max, pro)' )
    connect_sqlite3.commit ()

    for row in dicData:
        id = row['ID']
        name = row['name']
        mode = row['mode']
        type_ = row['var_type']
        coefficient = row['coefficient']
        len = row['len']
        V0 = row['V0']
        min = row['min']
        max = row['max']
        pro = row['pro']
        insert_data = [id, name, mode, type_, coefficient, len, V0, min, max, pro]
        coned.execute ('insert into rau values(?,?,?,?,?,?,?,?,?,?)', insert_data)
        connect_sqlite3.commit ()
    glob_data_sqlite3_use = dicData
    coned.close()

#FIXME: 在内存中维护一对兼职对进行使用
#FIXME: 初始化键值对,提供给全局的对象使用,方便数据的查询和使用;
# 在不适用redis 的情况下，本机小程序优先使用原生键值对的程序

def init_sql():
    creat_sql("rau.json", "rau_test.db")

def create_listen_web_func():
    print("listen params")

#TODO:使用udp与网关程序通信进行,对获取的数据解析之后然后再发送出去
# 然后对接受到的数据进行解析然后处理使用
#TODO:目前的线程相关的程序都是设备1对1的程序;完善对键值对的使用
#TODO:采用udp的方式进行进程中的通信;容易排查问题,且稳定传输进行使用
#TODO:传输完整的数据

def device_info_all() -> str: #从数据库筛选之后查看数据是否正确
    sql_querry_fd = sqlite3.connect("rau_test.db")
    sql_querry_cursor = sql_querry_fd.cursor()
    sql_querry_cursor.execute("select * from rau where pro == 1")
    tmp = sql_querry_cursor.fetchall()
    sql_querry_cursor.close()
    tmp_val = ""
    for row in tmp:
        for index in range(10):
            tmp_val += str(row[index])+"&"
        tmp_val += "end;"
    return tmp_val


def querry_list() -> str:
    sql_querry_fd = sqlite3.connect ( "rau_test.db" )
    sql_querry_cursor = sql_querry_fd.cursor ()
    sql_querry_cursor.execute ( "select * from rau" )
    tmp = sql_querry_cursor.fetchall ()
    sql_querry_cursor.close ()
    web_cmd_head = "cmd=para_list"
    tmp_val = ""
    for row in tmp:
        tmp_val +="&"+str(row[0])+"="+str(row[1])+","+str(row[3])+","+str(row[4])

    return web_cmd_head+tmp_val

def get_all_para() -> str:
    sql_querry_fd = sqlite3.connect ( "rau_test.db" )
    sql_querry_cursor = sql_querry_fd.cursor ()
    sql_querry_cursor.execute ( "select * from rau" )
    tmp = sql_querry_cursor.fetchall ()
    sql_querry_cursor.close ()
    web_cmd_head = "cmd=read"
    tmp_val = ""
    for row in tmp:
        tmp_val += "&"+str(row[0])+"="+str(row[6])
    return web_cmd_head+tmp_val

def set_para() -> str:
    return "ok"

def exec_func(data) -> str:
    exec_cmd = str(data).split(";") #得到了分割完成的字符串的数据;如果分割的字符串不完整;返回相关的错误代码
    print(exec_cmd)
    return "1"

#告警和告警使能放到一起进行设置和使用;根据ID NAME PRO 字段进行不同的区分
def get_alarm() -> str:
    web_cmd_head = "cmd=alarm_list"
    sql_querry_fd = sqlite3.connect ( "rau_test.db" )
    sql_querry_cursor = sql_querry_fd.cursor ()
    sql_querry_cursor.execute ( "select * from rau where pro == 100" )
    tmp = sql_querry_cursor.fetchall ()
    tmp_val = ""
    for row in tmp: #将查询到的PRO字段返回到;
        tmp_val += "&" + str ( row[0] ) + "=" + str ( row[1] ) + "," + str ( row[6] )+","+str(row[9])
    tmp.clear()
    sql_querry_cursor.execute("select * from rau where pro == 2")
    tmp = sql_querry_cursor.fetchall()
    for new_row in tmp:
        tmp_val += "&" + str ( new_row[0] ) + "=" + str ( new_row[1] ) + "," + str ( new_row[6] ) + "," + str ( new_row[9] )
    cmd = web_cmd_head+tmp_val
    return cmd

def com_set(data) -> str:
    return "ok :"+str(data)

#根据字典查询表进行数据的查询
func_dict = {"set":com_set,"device_info":device_info_all,"para_list":querry_list,"read":get_all_para,"write":set_para,"exec_script":exec_func,"alarm_list":get_alarm}

#将目前使用的结果处理完成探测

def udp_recv_parse(web_data) -> str:
    parse_data = web_data.decode('utf-8')
    if len(parse_data) == 2:
        return "error: dict is null" #查询回来的数据失败的使用的
    tmp = eval(parse_data)
    if tmp['cmd'][0] == "device_info": #将查询到的信息进行相应的返回操作并使用,对传递过来的数据进行解析
         var_tmp = func_dict.get('device_info')()
    elif tmp['cmd'][0] == "reboot":
        var_tmp = "res=reboot-successful"
    elif tmp['cmd'][0] == "para_list":
        var_tmp = func_dict.get('para_list')()
    elif tmp['cmd'][0] == "read":
        var_tmp = func_dict.get('read')()
    elif tmp['cmd'][0] == "write":
        print(tmp) #执行数据库写的操作
        var_tmp = func_dict.get('write')()
    elif tmp['cmd'][0] == "exec_script":
        var_tmp = func_dict.get("exec_script")(tmp['cmd'][1]) #解析cmd[1]的数据结构
    elif tmp['cmd'][0] == "alarm_list":
         var_tmp = func_dict.get('alarm_list')()
    elif tmp['cmd'][0] == "set":
          var_tmp = func_dict.get("set")(tmp)
    else :
        var_tmp = ""

   # var_tmp = "" #python3中字符串清空使用的方法 常用的字符串清空的方法


    return str(var_tmp)

def udp_send_packet(web_data) -> str:
    print(web_data)
    return web_data

def init_socket(addr:str,port) -> web_net:
    fd = web_net.socket(web_net.AF_INET, web_net.SOCK_DGRAM)
    fd.bind((addr, port))
    return fd

def web_deal():
    print("Web Deal Server was started")
    web_fd = init_socket("127.0.0.1", 8080)
    while True:
        data, web_cgi = web_fd.recvfrom(1024)
        parse_ok = udp_recv_parse(data)
        udp_send_packet(parse_ok)
        web_fd.sendto(parse_ok.encode('utf-8'),web_cgi)

#TODO:创建一个新的线程,整理到一个处理的对象中去进行使用:为以后小并发程序提供基础
# 北向接口的使用的方式,与网管通信 C++ 并发执行程序准备 可以同时处理两百台同时设备在线的请求操作

if __name__ == "__main__":
    init_sql()
    web_deal()